﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Text;
using Mk.CommonMethod;

namespace Dashboard.ProductBonus.Business
{
    public class UnproductiveTime : BusinessBase
    {
        public DataTable GetUnproductiveTime(string staffNo, DateTime dateFrom, DateTime dateTo, string team, string shift, string reasonCode)
        {
            string reasonCondition = string.Empty;
            if (!string.IsNullOrEmpty(reasonCode))
            {
                reasonCode = reasonCode.Split('-')[0];
                reasonCondition = " AND a.reasoncode = '" + base.FormatStringForSqlVariable(reasonCode) + "' ";
            }
            if (!string.IsNullOrEmpty(team))
            {
                reasonCondition += " AND n.team = '" + team + "' ";
            }
            if (!string.IsNullOrEmpty(shift))
            {
                reasonCondition += " AND n.shift = '" + shift + "' ";
            }
            string sql = "SELECT a.ID,a.StaffNo,n.name as StaffName, n.Team, n.Shift,b.pcode + '-' + b.pdesc as Reason," +
                        " a.Date,a.FromTime,a.ToTime,a.TotalTime,a.UpdatedBy,a.UpdatedDate " +
                        " FROM abnormalworkingtime a inner join namelist n on a.staffno=n.staffno " +
                        " left join NonworkingReason b on a.reasoncode=b.pcode and b.ptype='ReasonCode'" +
                        " where a.timetype <> 'Assistant' and a.staffno like '" + base.FormatStringForSqlVariable(staffNo) + "%' {0} " +
                        " and a.date >= '" + dateFrom.ToString("yyyy-MM-dd") + "' and a.date<='" + dateTo.ToString("yyyy-MM-dd") + "'" +
                        " order by a.date desc,n.Team,n.Shift,a.staffno,a.reasoncode";

            sql = string.Format(sql, reasonCondition);

            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable GetAssistantTime(string staffNo, DateTime dateFrom, DateTime dateTo, string team, string shift, string timeType)
        {
            string condition = string.Empty;
            if (!string.IsNullOrEmpty(team))
            {
                condition += " AND n.team = '" + team + "' ";
            }
            if (!string.IsNullOrEmpty(shift))
            {
                condition += " AND n.shift = '" + shift + "' ";
            }

            string sql = "SELECT a.ID,a.StaffNo,n.name as StaffName, n.Team, n.Shift,b.pcode + '-' + b.pdesc as Reason," +
                        " a.Date,a.FromTime,a.ToTime,a.TotalTime,a.UpdatedBy,a.UpdatedDate " +
                        " FROM abnormalworkingtime a inner join namelist n on a.staffno=n.staffno " +
                        " left join NonworkingReason b on a.reasoncode=b.pcode and b.ptype='ReasonCode'" +
                        " where a.timetype = '" + timeType + "' and a.staffno like '" + base.FormatStringForSqlVariable(staffNo) + "%' {0} " +
                        " and a.date >= '" + dateFrom.ToString("yyyy-MM-dd") + "' and a.date<='" + dateTo.ToString("yyyy-MM-dd") + "'" +
                        " order by a.date desc,n.Team,n.Shift,a.staffno,a.reasoncode";

            sql = string.Format(sql, condition);

            return DbConnection.DataSet(sql).Tables[0];
        }

        public void AddNewUnproductiveTimes(List<string> sqlList)
        {
            DbConnection.ExecuteSql(sqlList);
        }

        public void AddSingleUnproductiveTime(string sql)
        {
            DbConnection.ExecuteSql(sql);
        }

        public bool ExistUnproductiveTime(string staffNo, DateTime date, string reasonCode)
        {
            string sql = "SELECT count(*) FROM abnormalworkingtime WHERE reasoncode='" + reasonCode + "' and staffno = '" + staffNo + "' AND convert(varchar,date,112) = '" + date.ToString("yyyyMMdd") + "'";
            int count = Convert.ToInt32(DbConnection.GetValueOfField(sql));
            if (count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public void DeleteUnproductiveTime(string staffNo, DateTime date, string reasonCode,string timeType)
        {
            string sql = "DELETE FROM abnormalworkingtime WHERE TimeType='"+ timeType + "' and reasoncode='" + reasonCode + "' and staffno = '" + staffNo + "' AND convert(varchar,date,112) = '" + date.ToString("yyyyMMdd") + "'";
            DbConnection.ExecuteSql(sql);
        }
        public void DeleteUnproductiveTime(int id)
        {
            string sql = "DELETE FROM abnormalworkingtime WHERE id=" + id;
            DbConnection.ExecuteSql(sql);
        }

        public DataTable LoadStaffListForUnproductive(string team, string shift, string staffNo)
        {
            string sql = @"SELECT StaffNo,Name,Team,Shift,Convert(Date,getdate()-1) AS Date,
                                  '' AS Reason,0 as TotalTime,convert(varchar,null,120) as FromTime,convert(varchar,null,120) as ToTime 
                             From namelist 
                            WHERE 1 = 1
                                  {0}
                            ORDER BY Team,Shift,StaffNo
                        ";

            string condition = "";
            if (!string.IsNullOrEmpty(team))
            {
                condition += " AND team = '" + team + "' ";
            }
            if (!string.IsNullOrEmpty(shift))
            {
                condition += " AND shift = '" + shift + "' ";
            }
            if (!string.IsNullOrEmpty(staffNo))
            {
                condition += " AND StaffNo = '" + staffNo + "' ";
            }

            sql = string.Format(sql, condition);

            return DbConnection.DataSet(sql).Tables[0];
        }
    }
}
